package com.yumo.kangchenjunga.settlement;

import java.time.LocalDate;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface StatementDetailRepository extends JpaRepository<StatementDetailEntity, Integer> {

	@Query(nativeQuery = true, value = ""
			+ " select"
			+ "     company_id as companyId, company_name as companyName, store_id as storeId,"
			+ "     store_name as storeName, activity_id as activityId, activity_title as activityTitle,"
			+ "     count(1) as quantity"
			+ "   from coupon_log"
			+ "   where company_id = ?1"
			+ "     and op = 'redeem'"
			+ "     and op_result = 'success'"
			+ "     and date(convert_tz(op_time, @@session.time_zone, '+08:00')) between ?2 and ?3"
			+ "   group by store_id, activity_id"
			+ "   order by store_name, activity_title")
	List<StatementDetailProjection> queryDetails(int companyId, LocalDate startDate, LocalDate endDate);

}
